package com.tiachop.trongtruong.note.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.tiachop.trongtruong.note.item.ItemMoney;
import com.tiachop.trongtruong.note.item.ItemMoneyTopic;
import com.tiachop.trongtruong.note.item.ItemNote;
import com.tiachop.trongtruong.note.item.ItemNoteTopic;
import com.tiachop.trongtruong.note.item.ItemSetting;
import com.tiachop.trongtruong.note.item.ItemTopic;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHandler extends SQLiteOpenHelper {
 
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "NoteDatabase.db";
    private static SQLiteDatabase db;
 
    private static DatabaseHandler instance;
	private static final Object lockObject = new Object();

	/*---------------------------------------------------------------------------
	 * Define some information for table
	 */
	private static final String TABLE_MONEY = "money";
	private static final String TABLE_NOTE = "note";
	private static final String TABLE_TOPIC = "topic";
	private static final String TABLE_SETTING = "setting";
	private static final String TABLE_MONEY_TOPIC = "money_topic";
	private static final String TABLE_NOTE_TOPIC = "note_topic";
	private static final String TAG = "Database";
	
	// Common column names in table
	private static final String COLUMN_ID = "id";
	private static final String COLUMN_TITLE = "title";
	private static final String COLUMN_VALUE = "value";
	private static final String COLUMN_DESCRIPTION = "description";
	private static final String COLUMN_STATUS = "status";
	private static final String COLUMN_DATE = "date";
	private static final String COLUMN_TYPE = "type";
	private static final String COLUMN_ADDRESS = "address";
	private static final String COLUMN_LONGITUDE = "longitude";
	private static final String COLUMN_LATITUDE = "latitude";
	private static final String COLUMN_NOTIFY_STATUS = "notify_status";
	private static final String COLUMN_NOTIFY_DATE = "notify_date";
	private static final String COLUMN_MONEY_ID = "money_id";
	private static final String COLUMN_NOTE_ID = "note_id";
	private static final String COLUMN_TOPIC_ID = "topic_id";
	private static final String COLUMN_NAME= "name";
    private static final String COLUMN_INDEX= "index";


	private static final String CREATE_TABLE_MONEY = "CREATE TABLE " + TABLE_MONEY + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_TITLE + " TEXT,"
			+ COLUMN_VALUE + " REAL,"
			+ COLUMN_DESCRIPTION + " TEXT,"
			+ COLUMN_STATUS + " INTEGER,"
			+ COLUMN_DATE + " INTEGER,"
			+ COLUMN_TYPE + " INTEGER,"
			+ COLUMN_ADDRESS + " TEXT,"
			+ COLUMN_LONGITUDE + " TEXT,"
			+ COLUMN_LATITUDE + " TEXT,"
			+ ")";
	private static final String CREATE_TABLE_NOTE = "CREATE TABLE " + TABLE_NOTE + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_TITLE + " TEXT,"
			+ COLUMN_VALUE + " REAL,"
			+ COLUMN_DESCRIPTION + " TEXT,"
			+ COLUMN_STATUS + " INTEGER,"
			+ COLUMN_DATE + " INTEGER,"
			+ COLUMN_NOTIFY_STATUS + " INTEGER,"
			+ COLUMN_NOTIFY_DATE + " INTEGER,"
			+ COLUMN_ADDRESS + " TEXT,"
			+ COLUMN_LONGITUDE + " TEXT,"
			+ COLUMN_LATITUDE + " TEXT,"
			+ ")";
	private static final String CREATE_TABLE_TOPIC = "CREATE TABLE " + TABLE_TOPIC + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_NAME + " TEXT,"
			+ COLUMN_STATUS + " INTEGER,"
			+ ")";
	private static final String CREATE_TABLE_SETTING = "CREATE TABLE " + TABLE_SETTING + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_NAME + " TEXT,"
			+ COLUMN_VALUE + " TEXT,"
            + COLUMN_INDEX + " INTEGER,"
			+ ")";
	private static final String CREATE_TABLE_MONEY_TOPIC = "CREATE TABLE " + TABLE_MONEY_TOPIC + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_MONEY_ID + " INTEGER,"
			+ COLUMN_TOPIC_ID + " INTEGER,"
			+ COLUMN_DATE + " INTEGER,"
			+ ")";
	private static final String CREATE_TABLE_NOTE_TOPIC = "CREATE TABLE " + TABLE_NOTE_TOPIC + "("
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COLUMN_NOTE_ID + " INTEGER,"
			+ COLUMN_TOPIC_ID + " INTEGER,"
			+ COLUMN_DATE + " INTEGER,"
			+ ")";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
    	try{
			db.execSQL(CREATE_TABLE_MONEY);
			db.execSQL(CREATE_TABLE_NOTE);
			db.execSQL(CREATE_TABLE_TOPIC);
			db.execSQL(CREATE_TABLE_SETTING);
			db.execSQL(CREATE_TABLE_MONEY_TOPIC);
			db.execSQL(CREATE_TABLE_NOTE_TOPIC);
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MONEY);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TOPIC);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SETTING);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_MONEY_TOPIC);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE_TOPIC);
 
        // Create tables again
        onCreate(db);
    }

    //Open database
    public void openDatabase() {
		try {
			db = this.getWritableDatabase();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
    
    //Close database
    public void closeDatabase() {
		try {
			db.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
    
    /*---------------------------------------------------------------------------
	 * Implement CRUD for Money object
	 */
    public long addMoney(ItemMoney item) {
    	synchronized (lockObject) {
	    	openDatabase();

	        ContentValues values = new ContentValues();
	        values.put(COLUMN_TITLE, item.getTitle());
	        values.put(COLUMN_VALUE, item.getValue());
	        values.put(COLUMN_DESCRIPTION, item.getDescription());
	        values.put(COLUMN_STATUS, item.getStatus());
	        values.put(COLUMN_DATE, item.getDate());
	        values.put(COLUMN_TYPE, item.getType());
	        values.put(COLUMN_ADDRESS, item.getAddress());
	        values.put(COLUMN_LONGITUDE, item.getLongitude());
	        values.put(COLUMN_LATITUDE, item.getLatitude());

	        // Inserting Row
	        long todo_id =  db.insert(TABLE_MONEY, null, values);
	        closeDatabase();
	        return todo_id;
    	}
    }

	public int updateMoney(ItemMoney item) {
		synchronized (lockObject) {
			openDatabase();

			ContentValues values = new ContentValues();
			values.put(COLUMN_TITLE, item.getTitle());
			values.put(COLUMN_VALUE, item.getValue());
			values.put(COLUMN_DESCRIPTION, item.getDescription());
			values.put(COLUMN_STATUS, item.getStatus());
			values.put(COLUMN_DATE, item.getDate());
			values.put(COLUMN_TYPE, item.getType());
			values.put(COLUMN_ADDRESS, item.getAddress());
			values.put(COLUMN_LONGITUDE, item.getLongitude());
			values.put(COLUMN_LATITUDE, item.getLatitude());

			// Update Row
			int count = db.update(TABLE_MONEY, values, COLUMN_ID + "=" + item.getId(), null);
			closeDatabase();
			return count;
		}
	}

	public int deleteMoney(long id) {
		synchronized (lockObject) {
			openDatabase();
			int count = db.delete(TABLE_MONEY, COLUMN_ID + "="+ id,null);
			closeDatabase();
			return count;
		}
	}

	public ItemMoney getMoney(long id){
		synchronized (lockObject) {
			openDatabase();
			ItemMoney item = new ItemMoney();
			Cursor cursor = db.query(TABLE_MONEY, null, COLUMN_ID + "=" + id, null, null, null, null, null);
			if (cursor != null) cursor.moveToFirst();

			item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
			item.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
			item.setValue(cursor.getDouble(cursor.getColumnIndex(COLUMN_VALUE)));
			item.setType(cursor.getShort(cursor.getColumnIndex(COLUMN_TYPE)));
			item.setDate(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE)));
			item.setStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_STATUS)));
			item.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));
			item.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
			item.setLatitude(cursor.getString(cursor.getColumnIndex(COLUMN_LATITUDE)));
			item.setLongitude(cursor.getString(cursor.getColumnIndex(COLUMN_LONGITUDE)));

			closeDatabase();
			return item;
		}
	}

	public List<ItemMoney> getMoney(String keyWord,
									short status,
									short type,
									long startDate,
									long endDate,
									String topics,
									int pageIndex,
									int pageSize){
		String _selectQuery = "" ;
		ArrayList<String> params = new ArrayList<String>();
		boolean isHaveCondition = false;
		if(!keyWord.isEmpty()){
			isHaveCondition = true;
			_selectQuery += "(" + COLUMN_TITLE + " like '%?%'" + " or " + COLUMN_DESCRIPTION + " like '%?%'" + ")";
		}

		if(isHaveCondition) _selectQuery += " and ";
		if(status != -1) {
			isHaveCondition = true;
			_selectQuery += COLUMN_STATUS + "=?";
		}

		if(isHaveCondition) _selectQuery += " and ";
		if(type != -1){
			isHaveCondition = true;
			_selectQuery += COLUMN_TYPE + "=?";
		}

		if(isHaveCondition) _selectQuery += " and ";
		if(startDate != -1){
			isHaveCondition = true;
			_selectQuery += COLUMN_DATE + ">=?";
		}

		if(isHaveCondition) _selectQuery += " and ";
		if(endDate != -1){
			isHaveCondition = true;
			_selectQuery += COLUMN_DATE + "<=?";
		}

		if(isHaveCondition) _selectQuery += " and ";
		if(!topics.isEmpty()){
			isHaveCondition = true;
			_selectQuery += COLUMN_ID + "=" + COLUMN_MONEY_ID + " and " + COLUMN_TOPIC_ID + " in (?)";
		}

		String selectQuery = "SELECT  * FROM " + TABLE_MONEY
				+ " ORDER BY " + COLUMN_DATE + " DESC "
				+ " LIMIT ? " + " OFFSET ? ";
		if(_selectQuery.equals("")){
			selectQuery += " WHERE " + _selectQuery;
		}


		synchronized (lockObject) {
			openDatabase();
			params.add(String.valueOf(pageSize));
			params.add(String.valueOf(pageIndex * pageSize));
			if(!keyWord.isEmpty()) params.add(keyWord);
			if(status != -1) params.add(String.valueOf(status));
			if(type != -1) params.add(String.valueOf(type));
			if(startDate != -1) params.add(String.valueOf(startDate));
			if(endDate != -1) params.add(String.valueOf(endDate));
			if(!topics.isEmpty()) params.add(topics);

			List<ItemMoney> itemList = new ArrayList<ItemMoney>();
			Cursor cursor = null;
			try {
				cursor = db.rawQuery(selectQuery, params.toArray(new String[params.size()]));

				if (cursor != null && cursor.moveToFirst()) {
					do {
						ItemMoney item = new ItemMoney();
						item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
						item.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
						item.setValue(cursor.getDouble(cursor.getColumnIndex(COLUMN_VALUE)));
						item.setType(cursor.getShort(cursor.getColumnIndex(COLUMN_TYPE)));
						item.setDate(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE)));
						item.setStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_STATUS)));
						item.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));
						item.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
						item.setLatitude(cursor.getString(cursor.getColumnIndex(COLUMN_LATITUDE)));
						item.setLongitude(cursor.getString(cursor.getColumnIndex(COLUMN_LONGITUDE)));
						itemList.add(item);
					}
					while (cursor.moveToNext());
				}
			}
			catch (Exception e) {
				e.printStackTrace();
			}
			finally {
				if (cursor != null) {
					cursor.close();
				}
			}
			closeDatabase();
			return itemList;
		}
	}

    
	/*---------------------------------------------------------------------------
	 * Implement CRUD for Note object
	 */

    public long addNote(ItemNote item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_TITLE, item.getTitle());
            values.put(COLUMN_DESCRIPTION, item.getDescription());
            values.put(COLUMN_DATE, item.getDate());
            values.put(COLUMN_NOTIFY_STATUS, item.getNotifyStatus());
            values.put(COLUMN_NOTIFY_DATE, item.getNotifyDate());
            values.put(COLUMN_STATUS, item.getStatus());
            values.put(COLUMN_ADDRESS, item.getAddress());
            values.put(COLUMN_LONGITUDE, item.getLongitude());
            values.put(COLUMN_LATITUDE, item.getLatitude());

            // Inserting Row
            long todo_id =  db.insert(TABLE_NOTE, null, values);
            closeDatabase();
            return todo_id;
        }
    }

    public int updateNote(ItemNote item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_TITLE, item.getTitle());
            values.put(COLUMN_DESCRIPTION, item.getDescription());
            values.put(COLUMN_DATE, item.getDate());
            values.put(COLUMN_NOTIFY_STATUS, item.getNotifyStatus());
            values.put(COLUMN_NOTIFY_DATE, item.getNotifyDate());
            values.put(COLUMN_STATUS, item.getStatus());
            values.put(COLUMN_ADDRESS, item.getAddress());
            values.put(COLUMN_LONGITUDE, item.getLongitude());
            values.put(COLUMN_LATITUDE, item.getLatitude());

            // Update Row
            int count = db.update(TABLE_NOTE, values, COLUMN_ID + "=" + item.getId(), null);
            closeDatabase();
            return count;
        }
    }

    public int deleteNote(long id) {
        synchronized (lockObject) {
            openDatabase();
            int count = db.delete(TABLE_NOTE, COLUMN_ID + "="+ id,null);
            closeDatabase();
            return count;
        }
    }
    public ItemNote getNote(long id){
        synchronized (lockObject) {
            openDatabase();
            ItemNote item = new ItemNote();
            Cursor cursor = db.query(TABLE_NOTE, null, COLUMN_ID + "=" + id, null, null, null, null, null);
            if (cursor != null) cursor.moveToFirst();

            item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
            item.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            item.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));
            item.setDate(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE)));
            item.setNotifyStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_NOTIFY_STATUS)));
            item.setNotifyDate(cursor.getLong(cursor.getColumnIndex(COLUMN_NOTIFY_DATE)));
            item.setStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_STATUS)));
            item.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
            item.setLatitude(cursor.getString(cursor.getColumnIndex(COLUMN_LATITUDE)));
            item.setLongitude(cursor.getString(cursor.getColumnIndex(COLUMN_LONGITUDE)));

            closeDatabase();
            return item;
        }
    }

    public List<ItemNote> getNote(String keyWord,
                                    short status,
                                    short notifyStatus,
                                    long startDate,
                                    long endDate,
                                    String topics,
                                    int pageIndex,
                                    int pageSize) {
		String _selectQuery = "";
		ArrayList<String> params = new ArrayList<String>();
		boolean isHaveCondition = false;
		if (!keyWord.isEmpty()) {
			isHaveCondition = true;
			_selectQuery += "(" + COLUMN_TITLE + " like '%?%'" + " or " + COLUMN_DESCRIPTION + " like '%?%'" + ")";
		}

		if (isHaveCondition) _selectQuery += " and ";
		if (status != -1) {
			isHaveCondition = true;
			_selectQuery += COLUMN_STATUS + "=?";
		}

		if (isHaveCondition) _selectQuery += " and ";
		if (notifyStatus != -1) {
			isHaveCondition = true;
			_selectQuery += COLUMN_NOTIFY_STATUS + "=?";
		}

		if (isHaveCondition) _selectQuery += " and ";
		if (startDate != -1) {
			isHaveCondition = true;
			_selectQuery += COLUMN_DATE + ">=?";
		}

		if (isHaveCondition) _selectQuery += " and ";
		if (endDate != -1) {
			isHaveCondition = true;
			_selectQuery += COLUMN_DATE + "<=?";
		}

		if (isHaveCondition) _selectQuery += " and ";
		if (!topics.isEmpty()) {
			isHaveCondition = true;
			_selectQuery += COLUMN_ID + "=" + COLUMN_NOTE_ID + " and " + COLUMN_TOPIC_ID + " in (?)";
		}

		String selectQuery = "SELECT  * FROM " + TABLE_NOTE
				+ " ORDER BY " + COLUMN_DATE + " DESC "
				+ " LIMIT ? " + " OFFSET ? ";
		if (_selectQuery.equals("")) {
			selectQuery += " WHERE " + _selectQuery;
		}


		synchronized (lockObject) {
			openDatabase();
			params.add(String.valueOf(pageSize));
			params.add(String.valueOf(pageIndex * pageSize));
			if (!keyWord.isEmpty()) params.add(keyWord);
			if (status != -1) params.add(String.valueOf(status));
			if (notifyStatus != -1) params.add(String.valueOf(notifyStatus));
			if (startDate != -1) params.add(String.valueOf(startDate));
			if (endDate != -1) params.add(String.valueOf(endDate));
			if (!topics.isEmpty()) params.add(topics);

			List<ItemNote> itemList = new ArrayList<ItemNote>();
			Cursor cursor = null;
			try {
				cursor = db.rawQuery(selectQuery, params.toArray(new String[params.size()]));

				if (cursor != null && cursor.moveToFirst()) {
					do {
                        ItemNote item = new ItemNote();
						item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
						item.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
						item.setDescription(cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION)));
						item.setDate(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE)));
						item.setNotifyStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_NOTIFY_STATUS)));
						item.setNotifyDate(cursor.getLong(cursor.getColumnIndex(COLUMN_NOTIFY_DATE)));
						item.setStatus(cursor.getShort(cursor.getColumnIndex(COLUMN_STATUS)));
						item.setAddress(cursor.getString(cursor.getColumnIndex(COLUMN_ADDRESS)));
						item.setLatitude(cursor.getString(cursor.getColumnIndex(COLUMN_LATITUDE)));
						item.setLongitude(cursor.getString(cursor.getColumnIndex(COLUMN_LONGITUDE)));
						itemList.add(item);
					}
					while (cursor.moveToNext());
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (cursor != null) {
					cursor.close();
				}
			}
			closeDatabase();
			return itemList;
		}

	}

    /*---------------------------------------------------------------------------
	 * Implement CRUD for Topic object
	 */
    public long addTopic(ItemTopic item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NAME, item.getName());
            values.put(COLUMN_STATUS, item.getStatus());

            // Inserting Row
            long todo_id =  db.insert(TABLE_TOPIC, null, values);
            closeDatabase();
            return todo_id;
        }
    }

    public int updateTopic(ItemTopic item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NAME, item.getName());
            values.put(COLUMN_STATUS, item.getStatus());

            // Update Row
            int count = db.update(TABLE_TOPIC, values, COLUMN_ID + "=" + item.getId(), null);
            closeDatabase();
            return count;
        }
    }

    public int deleteTopic(long id) {
        synchronized (lockObject) {
            openDatabase();
            int count = db.delete(TABLE_TOPIC, COLUMN_ID + "=" + id, null);
            closeDatabase();
            return count;
        }
    }
    public ItemTopic getTopic(long id){
        synchronized (lockObject) {
            openDatabase();
            ItemTopic item = new ItemTopic();
            Cursor cursor = db.query(TABLE_TOPIC, null, COLUMN_ID + "=" + id, null, null, null, null, null);
            if (cursor != null) cursor.moveToFirst();
            item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
            item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));

            closeDatabase();
            return item;
        }
    }
    public List<ItemTopic> getTopic(String keyWord,
                                  short status,
                                  int pageIndex,
                                  int pageSize) {
        String _selectQuery = "";
        ArrayList<String> params = new ArrayList<String>();
        boolean isHaveCondition = false;
        if (!keyWord.isEmpty()) {
            isHaveCondition = true;
            _selectQuery += "(" + COLUMN_NAME + " like '%?%'" + ")";
        }

        if (isHaveCondition) _selectQuery += " and ";
        if (status != -1) {
            isHaveCondition = true;
            _selectQuery += COLUMN_STATUS + "=?";
        }

        String selectQuery = "SELECT  * FROM " + TABLE_TOPIC
                + " ORDER BY " + COLUMN_NAME+ " ASC "
                + " LIMIT ? " + " OFFSET ? ";
        if (_selectQuery.equals("")) {
            selectQuery += " WHERE " + _selectQuery;
        }


        synchronized (lockObject) {
            openDatabase();
            params.add(String.valueOf(pageSize));
            params.add(String.valueOf(pageIndex * pageSize));
            if (!keyWord.isEmpty()) params.add(keyWord);
            if (status != -1) params.add(String.valueOf(status));
            List<ItemTopic> itemList = new ArrayList<ItemTopic>();
            Cursor cursor = null;
            try {
                cursor = db.rawQuery(selectQuery, params.toArray(new String[params.size()]));

                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        ItemTopic item = new ItemTopic();
                        item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
                        item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                        itemList.add(item);
                    }
                    while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
            }
            closeDatabase();
            return itemList;
        }
    }
    public List<ItemTopic> getTopicOfMoney(long moneyId){
        synchronized (lockObject) {
            openDatabase();
            List<ItemTopic> itemList = new ArrayList<ItemTopic>();
            String selectQuery = "SELECT  * FROM " + TABLE_TOPIC
                    + " ORDER BY " + COLUMN_NAME+ " ASC "
                    + " WHERE  " + COLUMN_MONEY_ID + "=" + moneyId;

            Cursor cursor = null;
            try {
                cursor = db.rawQuery(selectQuery,null);

                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        ItemTopic item = new ItemTopic();
                        item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
                        item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                        itemList.add(item);
                    }
                    while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
            }
            closeDatabase();
            return itemList;
        }
    }
    public List<ItemTopic> getTopicOfNote(long noteId) {
        synchronized (lockObject) {
            openDatabase();
            List<ItemTopic> itemList = new ArrayList<ItemTopic>();
            String selectQuery = "SELECT  * FROM " + TABLE_TOPIC
                    + " ORDER BY " + COLUMN_NAME + " ASC "
                    + " WHERE  " + COLUMN_NOTE_ID + "=" + noteId;

            Cursor cursor = null;
            try {
                cursor = db.rawQuery(selectQuery, null);

                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        ItemTopic item = new ItemTopic();
                        item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
                        item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                        itemList.add(item);
                    }
                    while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
            }
            closeDatabase();
            return itemList;
        }
    }
	/*---------------------------------------------------------------------------
	 * Implement CRUD for MoneyTopic object
	 */
	public long addMoneyTopic(ItemMoneyTopic item) {
		synchronized (lockObject) {
			openDatabase();

			ContentValues values = new ContentValues();
			values.put(COLUMN_MONEY_ID, item.getMoneyId());
			values.put(COLUMN_TOPIC_ID, item.getTopicId());
            values.put(COLUMN_DATE, item.getDate());

			// Inserting Row
			long todo_id =  db.insert(TABLE_MONEY_TOPIC, null, values);
			closeDatabase();
			return todo_id;
		}
	}

	public int updateMoneyTopic(ItemMoneyTopic item) {
		synchronized (lockObject) {
			openDatabase();

			ContentValues values = new ContentValues();
            values.put(COLUMN_MONEY_ID, item.getMoneyId());
            values.put(COLUMN_TOPIC_ID, item.getTopicId());
            values.put(COLUMN_DATE,item.getDate());

			// Update Row
			int count = db.update(TABLE_MONEY_TOPIC, values, COLUMN_ID + "=" + item.getId(), null);
			closeDatabase();
			return count;
		}
	}

	public int deleteMoneyTopic(long id) {
		synchronized (lockObject) {
			openDatabase();
			int count = db.delete(TABLE_MONEY_TOPIC, COLUMN_ID + "=" + id, null);
			closeDatabase();
			return count;
		}
	}
    /*---------------------------------------------------------------------------
     * Implement CRUD for NoteTopic object
     */
    public long addNoteTopic(ItemNoteTopic item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NOTE_ID, item.getNoteId());
            values.put(COLUMN_TOPIC_ID, item.getTopicId());
            values.put(COLUMN_DATE, item.getDate());

            // Inserting Row
            long todo_id =  db.insert(TABLE_NOTE_TOPIC, null, values);
            closeDatabase();
            return todo_id;
        }
    }

    public int updateMoneyTopic(ItemNoteTopic item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NOTE_ID, item.getNoteId());
            values.put(COLUMN_TOPIC_ID, item.getTopicId());
            values.put(COLUMN_DATE,item.getDate());

            // Update Row
            int count = db.update(TABLE_NOTE_TOPIC, values, COLUMN_ID + "=" + item.getId(), null);
            closeDatabase();
            return count;
        }
    }

    public int deleteNoteTopic(long id) {
        synchronized (lockObject) {
            openDatabase();
            int count = db.delete(TABLE_NOTE_TOPIC, COLUMN_ID + "=" + id, null);
            closeDatabase();
            return count;
        }
    }

    /*---------------------------------------------------------------------------
	 * Implement CRUD for Setting object
	 */
    public long addSetting(ItemSetting item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NAME, item.getName());
            values.put(COLUMN_VALUE, item.getValue());

            // Inserting Row
            long todo_id =  db.insert(TABLE_SETTING, null, values);
            closeDatabase();
            return todo_id;
        }
    }

    public int updateSetting(ItemSetting item) {
        synchronized (lockObject) {
            openDatabase();

            ContentValues values = new ContentValues();
            values.put(COLUMN_NAME, item.getName());
            values.put(COLUMN_VALUE, item.getValue());

            // Update Row
            int count = db.update(TABLE_SETTING, values, COLUMN_ID + "=" + item.getId(), null);
            closeDatabase();
            return count;
        }
    }

    public int deleteSetting(long id) {
        synchronized (lockObject) {
            openDatabase();
            int count = db.delete(TABLE_SETTING, COLUMN_ID + "=" + id, null);
            closeDatabase();
            return count;
        }
    }
    public ItemSetting getSetting(short index){
        synchronized (lockObject) {
            openDatabase();
            ItemSetting item = new ItemSetting();
            Cursor cursor = db.query(TABLE_TOPIC, null, COLUMN_INDEX + "=" + index, null, null, null, null, null);
            if (cursor != null) cursor.moveToFirst();
            item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
            item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            item.setValue(cursor.getString(cursor.getColumnIndex(COLUMN_VALUE)));
            item.setIndex(cursor.getShort(cursor.getColumnIndex(COLUMN_INDEX)));

            closeDatabase();
            return item;
        }
    }
    public List<ItemSetting> getSetting(){
        synchronized (lockObject) {
            openDatabase();
            List<ItemSetting> itemList = new ArrayList<ItemSetting>();
            String selectQuery = "SELECT  * FROM " + TABLE_SETTING
                    + " ORDER BY " + COLUMN_NAME+ " ASC ";

            Cursor cursor = null;
            try {
                cursor = db.rawQuery(selectQuery,null);

                if (cursor != null && cursor.moveToFirst()) {
                    do {
                        ItemSetting item = new ItemSetting();
                        item.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
                        item.setName(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
                        item.setValue(cursor.getString(cursor.getColumnIndex(COLUMN_VALUE)));
                        item.setIndex(cursor.getShort(cursor.getColumnIndex(COLUMN_INDEX)));
                        itemList.add(item);
                    }
                    while (cursor.moveToNext());
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (cursor != null) {
                    cursor.close();
                }
            }
            closeDatabase();
            return itemList;
        }
    }
}
